﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace VietKinsNutrition.Data
{
    public class ClsTblDiemdanhtreanDAL
    {
        ClsBindingBLL ClsBin = new ClsBindingBLL();
        ClsTblLophocDAL ClsTblLophocdal = new ClsTblLophocDAL();

        DataSet SelectTblDiemdanhtreanNullDiemdanhtreanId(int NamhocId, int LophocId, int ThanghocId, DateTime Ngaydiemdanh)
        {
            string NamePrc = "SELECT Hovaten, Tenthuonggoi, NamtrehocId, LophocId ,  Vw_Tre_namtrehoc.Tendantoc, Vw_Tre_namtrehoc.Ngaysinh, Vw_Tre_namtrehoc.Gioitinh FROM Vw_Tre_namtrehoc WHERE NamhocId = " + NamhocId.ToString() + " AND LophocId = " + LophocId.ToString();
            string NameTable = "TblTrenoptunguyen";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = true;
            NewTable.Columns.Add(Columm);



            DataColumn Id = new DataColumn();
            Id.ColumnName = Columm.Caption = "Id";
            Id.DataType = System.Type.GetType("System.Int32");
            Id.AllowDBNull = true;
            NewTable.Columns.Add(Id);

            DataColumn NamhoctreId = new DataColumn();
            NamhoctreId.ColumnName = Columm.Caption = "NamtrehocId";
            NamhoctreId.DataType = System.Type.GetType("System.Int32");
            NamhoctreId.AllowDBNull = true;
            NewTable.Columns.Add(NamhoctreId);

            DataColumn _LophocId = new DataColumn();
            _LophocId.ColumnName = Columm.Caption = "LophocId";
            _LophocId.DataType = System.Type.GetType("System.Int32");
            _LophocId.AllowDBNull = true;
            NewTable.Columns.Add(_LophocId);



            DataColumn Hovaten = new DataColumn();
            Hovaten.ColumnName = Columm.Caption = "Hovaten";
            Hovaten.DataType = System.Type.GetType("System.String");
            Hovaten.AllowDBNull = true;
            NewTable.Columns.Add(Hovaten);

            DataColumn Tenthuonggoi = new DataColumn();
            Tenthuonggoi.ColumnName = Columm.Caption = "Tenthuonggoi";
            Tenthuonggoi.DataType = System.Type.GetType("System.String");
            Tenthuonggoi.AllowDBNull = true;
            NewTable.Columns.Add(Tenthuonggoi);

            DataColumn Tinhtrang = new DataColumn();
            Tinhtrang.ColumnName = Columm.Caption = "Trangthai";
            Tinhtrang.DataType = System.Type.GetType("System.Boolean");
            Tinhtrang.AllowDBNull = true;
            NewTable.Columns.Add(Tinhtrang);


            DataColumn Ngay = new DataColumn();
            Ngay.ColumnName = Columm.Caption = "Ngay";
            Ngay.DataType = System.Type.GetType("System.DateTime");
            Ngay.AllowDBNull = true;
            NewTable.Columns.Add(Ngay);





            DataColumn _ThanghocId = new DataColumn();
            _ThanghocId.ColumnName = Columm.Caption = "ThanghocId";
            _ThanghocId.DataType = System.Type.GetType("System.Int32");
            _ThanghocId.AllowDBNull = true;
            NewTable.Columns.Add(_ThanghocId);

            DataColumn _NamhocId = new DataColumn();
            _NamhocId.ColumnName = Columm.Caption = "NamhocId";
            _NamhocId.DataType = System.Type.GetType("System.Int32");
            _NamhocId.AllowDBNull = true;
            NewTable.Columns.Add(_NamhocId);


            DataSet _Dset = new DataSet();
            OleDbCommand Cmm = new OleDbCommand();
            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(_Dset);
            if (_Dset.Tables[0].Rows.Count > 0)
            {
                int i = 0;
                foreach (DataRow Row in _Dset.Tables[0].Rows)
                {
                    i = i + 1;
                    NewTable.Rows.Add(i, null, Row["NamtrehocId"], Row["LophocId"], Row["Hovaten"], Row["Tenthuonggoi"],
                        false, Ngaydiemdanh, ThanghocId, NamhocId);
                }
            }
            Dset.Tables.Add(NewTable);
            return Dset;
        }

        public DataSet SelectTblTienanthuctetrongthang(int NamhocId, int LophocId, int ThanghocId, int NamtrehocId)
        {
            DataSet ReturnSet = new DataSet();
            string NamePrc = QueryString.SPROC_TBLTIENANTHUCTECHOTHANG_GET;
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@NamhocId", NamhocId));
            Cmm.Parameters.Add(new OleDbParameter("@LophocId", LophocId));
            Cmm.Parameters.Add(new OleDbParameter("@ThanghocId", ThanghocId));
            Cmm.Parameters.Add(new OleDbParameter("@NamtrehocId", NamtrehocId));
            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }

            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);

            return Dset;
        }

        public DataTable TblDsTreMoi(int lophocId, int IdNamhocMoi)
        {
            DataSet ReturnSet = new DataSet();
            string NamePrc = "SELECT TblNamtrehoc.TreId, TblNamtrehoc.LophocId, TblNamtrehoc.NamhocId FROM TblNamtrehoc WHERE TblNamtrehoc.LophocId=" + lophocId.ToString() + " AND TblNamtrehoc.NamhocId=" + IdNamhocMoi.ToString() + "";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();
            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }

            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);

            return Dset.Tables[0];
        }

        public DataTable TblDsTre(int lophocId, int IdNamhocMOi)
        {
            DataSet ReturnSet = new DataSet();
            string NamePrc = "SELECT TblLenlop.TreId, TblLenlop.NamtrehocId, TblLenlop.LophocId, TblLenlop.Hovaten, TblLenlop.Trangthai, IIf(IsNull(Lenlop),false,true) AS IsLenlop " +
" FROM (SELECT Vw_Tre_namtrehoc.TreId, Vw_Tre_namtrehoc.NamtrehocId, Vw_Tre_namtrehoc.LophocId, Vw_Tre_namtrehoc.Hovaten, IIf(IsNull(Vw_Tre_namtrehoc.Tinhtrang),False,False) AS Trangthai " +
" ,(select top 1 TreId  from  TblNamtrehoc where NamhocId =" + IdNamhocMOi.ToString() + " and Vw_Tre_namtrehoc.TreId =TreId ) AS Lenlop " +
 " FROM Vw_Tre_namtrehoc WHERE (((Vw_Tre_namtrehoc.LophocId)=" + lophocId.ToString() + "))) AS TblLenlop";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();
            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }

            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);

            return Dset.Tables[0];
        }

        public DataSet SelectTblTienanthuctetrongngay(int NamhocId, int LophocId, DateTime Ngaydiemdanh, int NamtrehocId)
        {
            DataSet ReturnSet = new DataSet();
            string NamePrc = QueryString.SPROC_TBLTIENANTHUCTECHONGAY_GET;
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@NamhocId", NamhocId));
            Cmm.Parameters.Add(new OleDbParameter("@LophocId", LophocId));
            Cmm.Parameters.Add(new OleDbParameter("@Ngay", Ngaydiemdanh));
            Cmm.Parameters.Add(new OleDbParameter("@NamtrehocId", NamtrehocId));
            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }

            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);

            return Dset;
        }

        public DataSet SelectTblTienanchotre(int NamhocId, int LophocId, DateTime Ngaydiemdanh)
        {

            DataSet ReturnSet = new DataSet();

            string NamePrc = QueryString.SPROC_TBLTIENANCUATRE_GET;
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@NamhocId", NamhocId));
            Cmm.Parameters.Add(new OleDbParameter("@LophocId", LophocId));
            Cmm.Parameters.Add(new OleDbParameter("@Ngay", Ngaydiemdanh));
            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }

            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);

            return Dset;
        }
        
        public DataSet ReturnDiemdanhtrean()
        {
            string NamePrc = QueryString.SPROC_TBLTREPHAITHUNAM;
            string NameTable = "TblTrenoptunguyen";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = true;
            NewTable.Columns.Add(Columm);

            DataColumn Id = new DataColumn();
            Id.ColumnName = Columm.Caption = "Id";
            Id.DataType = System.Type.GetType("System.Int32");
            Id.AllowDBNull = true;
            NewTable.Columns.Add(Id);

            DataColumn NamhoctreId = new DataColumn();
            NamhoctreId.ColumnName = Columm.Caption = "NamtrehocId";
            NamhoctreId.DataType = System.Type.GetType("System.Int32");
            NamhoctreId.AllowDBNull = true;
            NewTable.Columns.Add(NamhoctreId);

            DataColumn _LophocId = new DataColumn();
            _LophocId.ColumnName = Columm.Caption = "LophocId";
            _LophocId.DataType = System.Type.GetType("System.Int32");
            _LophocId.AllowDBNull = true;
            NewTable.Columns.Add(_LophocId);

            DataColumn Hovaten = new DataColumn();
            Hovaten.ColumnName = Columm.Caption = "Hovaten";
            Hovaten.DataType = System.Type.GetType("System.String");
            Hovaten.AllowDBNull = true;
            NewTable.Columns.Add(Hovaten);

            DataColumn Tenthuonggoi = new DataColumn();
            Tenthuonggoi.ColumnName = Columm.Caption = "Tenthuonggoi";
            Tenthuonggoi.DataType = System.Type.GetType("System.String");
            Tenthuonggoi.AllowDBNull = true;
            NewTable.Columns.Add(Tenthuonggoi);

            DataColumn Tinhtrang = new DataColumn();
            Tinhtrang.ColumnName = Columm.Caption = "Tinhtrang";
            Tinhtrang.DataType = System.Type.GetType("System.Boolean");
            Tinhtrang.AllowDBNull = true;
            NewTable.Columns.Add(Tinhtrang);

            DataColumn Ngay = new DataColumn();
            Ngay.ColumnName = Columm.Caption = "Ngay";
            Ngay.DataType = System.Type.GetType("System.DateTime");
            Ngay.AllowDBNull = true;
            NewTable.Columns.Add(Ngay);


            DataColumn _BuaantrongngayId = new DataColumn();
            _BuaantrongngayId.ColumnName = Columm.Caption = "BuaantrongngayId";
            _BuaantrongngayId.DataType = System.Type.GetType("System.Int32");
            _BuaantrongngayId.AllowDBNull = true;
            NewTable.Columns.Add(_BuaantrongngayId);


            DataColumn _ThanghocId = new DataColumn();
            _ThanghocId.ColumnName = Columm.Caption = "ThanghocId";
            _ThanghocId.DataType = System.Type.GetType("System.Int32");
            _ThanghocId.AllowDBNull = true;
            NewTable.Columns.Add(_ThanghocId);

            DataColumn _NamhocId = new DataColumn();
            _NamhocId.ColumnName = Columm.Caption = "NamhocId";
            _NamhocId.DataType = System.Type.GetType("System.Int32");
            _NamhocId.AllowDBNull = true;
            NewTable.Columns.Add(_NamhocId);

            Dset.Tables.Add(NewTable);
            return Dset;
        }

        public DataTable TblSongaydihoccuatretrongthang(int NamhocId, int LophocId, int ThanghocId)
        {
            DataSet ReturnSet = new DataSet();

            string NamePrc = "SELECT Vw_Tre_namtrehoc.Hovaten, Vw_Tre_namtrehoc.Ngaysinh, Vw_Tre_namtrehoc.Gioitinh, Vw_Tre_namtrehoc.Tendantoc, IIf(IsNull([TblDiemdantredihoc.Tongsobuoidi]),0,[TblDiemdantredihoc.Tongsobuoidi]) AS Tongsobuoidi, IIf(IsNull([nghi.Tongsobuoinghi]),0,[nghi.Tongsobuoinghi]) AS Tongsobuoinghi FROM (Vw_Tre_namtrehoc LEFT JOIN [SELECT     NamtrehocId, COUNT(NamtrehocId) AS Tongsobuoidi  FROM  ( SELECT     TblDiemdanhtrehoc.Id, TblNamtrehoc.Id AS NamtrehocId, TblNamtrehoc.LophocId, TblTre.Hovaten,  TblTre.Tenthuonggoi, TblDiemdanhtrehoc.Trangthai, TblDiemdanhtrehoc.Ngay, TblDiemdanhtrehoc.ThanghocId, TblNamtrehoc.NamhocId  FROM  (TblTre INNER JOIN   TblNamtrehoc ON TblTre.Id = TblNamtrehoc.TreId)  INNER JOIN  TblDiemdanhtrehoc ON TblNamtrehoc.Id = TblDiemdanhtrehoc.NamtrehocId  WHERE      (TblNamtrehoc.NamhocId =" + NamhocId + ") AND (TblNamtrehoc.LophocId = " + LophocId + ") AND (TblTre.Tinhtrang = 0) AND  (TblDiemdanhtrehoc.ThanghocId = " + ThanghocId + ")  ) AS TblTredihoc          WHERE      (Trangthai = -1)  GROUP BY NamtrehocId, Trangthai ]. AS TblDiemdantredihoc ON Vw_Tre_namtrehoc.NamtrehocId = TblDiemdantredihoc.NamtrehocId) LEFT JOIN [SELECT     NamtrehocId, COUNT(NamtrehocId) AS Tongsobuoinghi  FROM  ( SELECT     TblDiemdanhtrehoc.Id, TblNamtrehoc.Id AS NamtrehocId, TblNamtrehoc.LophocId, TblTre.Hovaten,  TblTre.Tenthuonggoi, TblDiemdanhtrehoc.Trangthai, TblDiemdanhtrehoc.Ngay, TblDiemdanhtrehoc.ThanghocId, TblNamtrehoc.NamhocId  FROM  (TblTre INNER JOIN   TblNamtrehoc ON TblTre.Id = TblNamtrehoc.TreId)  INNER JOIN  TblDiemdanhtrehoc ON TblNamtrehoc.Id = TblDiemdanhtrehoc.NamtrehocId  WHERE      (TblNamtrehoc.NamhocId = " + NamhocId + ") AND (TblNamtrehoc.LophocId = " + LophocId + ") AND (TblTre.Tinhtrang = 0) AND  (TblDiemdanhtrehoc.ThanghocId = " + ThanghocId + ")  ) AS TblTredihoc          WHERE      (Trangthai = 0)  GROUP BY NamtrehocId, Trangthai ]. AS nghi ON Vw_Tre_namtrehoc.NamtrehocId = nghi.NamtrehocId GROUP BY Vw_Tre_namtrehoc.Hovaten, Vw_Tre_namtrehoc.Ngaysinh, Vw_Tre_namtrehoc.Gioitinh, Vw_Tre_namtrehoc.Tendantoc, IIf(IsNull([TblDiemdantredihoc.Tongsobuoidi]),0,[TblDiemdantredihoc.Tongsobuoidi]), IIf(IsNull([nghi.Tongsobuoinghi]),0,[nghi.Tongsobuoinghi]), Vw_Tre_namtrehoc.LophocId HAVING (((Vw_Tre_namtrehoc.LophocId)=" + LophocId + "))";

            string NameTable = "TblDiemdanhtrean";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();

            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }

            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);
            return Dset.Tables[0];
        }
        
        public DataSet SelectTblDiemdanhtrean(int NamhocId, int LophocId, int ThanghocId, DateTime Ngaydiemdanh)
        {

            DataSet ReturnSet = new DataSet();

            string NamePrc =

                "SELECT TblTrethamgiaan.Id, Vw_Tre_namtrehoc.NamtrehocId, Vw_Tre_namtrehoc.LophocId, Vw_Tre_namtrehoc.Hovaten, Vw_Tre_namtrehoc.Tenthuonggoi,  Vw_Tre_namtrehoc.Ngaysinh, Vw_Tre_namtrehoc.Gioitinh,Vw_Tre_namtrehoc.Tendantoc, TblTrethamgiaan.Trangthai, TblTrethamgiaan.Ngay, TblTrethamgiaan.ThanghocId, Vw_Tre_namtrehoc.NamhocId " +
" FROM Vw_Tre_namtrehoc LEFT JOIN [SELECT TblDiemdanhtrean.Id, TblDiemdanhtrean.NamtrehocId, TblDiemdanhtrean.Ngay, TblDiemdanhtrean.ThanghocId, TblDiemdanhtrean.Trangthai " +
" FROM TblDiemdanhtrean " +
" WHERE ( ((TblDiemdanhtrean.Ngay)=#" + Ngaydiemdanh.ToShortDateString() + "#) AND ((TblDiemdanhtrean.ThanghocId)=" + ThanghocId + "))]. AS TblTrethamgiaan ON Vw_Tre_namtrehoc.NamtrehocId = TblTrethamgiaan.NamtrehocId " +
" WHERE (((Vw_Tre_namtrehoc.LophocId)=" + LophocId + "))";


            string NameTable = "TblDiemdanhtrean";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();
            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }

            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);
            try
            {
                foreach (DataRow rw in Dset.Tables[0].Rows)
                {
                    rw["Ngay"] = Ngaydiemdanh;
                }
            }
            catch { }
            return Dset;
        }

        public DataSet SelectTblLenloptredihoc(int NamhocId, int LophocId, int ThanghocId, DateTime Ngaydiemdanh)
        {

            DataSet ReturnSet = new DataSet();
            string NamePrc;
            if (LophocId == -1)
            {
                NamePrc = QueryString.SPROC_TBLLenlopTREDIHOC_GET2.Replace("@NamhocId", NamhocId.ToString()).Replace("@LophocId", LophocId.ToString()).Replace("@ThanghocId", ThanghocId.ToString()).Replace("@Ngay", Ngaydiemdanh.ToShortDateString());
            }
            else
            {
                NamePrc = QueryString.SPROC_TBLLenlopTREDIHOC_GET1.Replace("@NamhocId", NamhocId.ToString()).Replace("@LophocId", LophocId.ToString()).Replace("@ThanghocId", ThanghocId.ToString()).Replace("@Ngay", Ngaydiemdanh.ToShortDateString());
            }

            string NameTable = "TblLenloptredihoc";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();

            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }

            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);
            DataSet Set1 = this.SelectTblDiemdanhtreanNullDiemdanhtreanId(NamhocId, LophocId, ThanghocId, Ngaydiemdanh);

            bool CheckNewHs = true;
            if (Dset.Tables[0].Rows.Count > 0)
            {
                if (Set1.Tables[0].Rows.Count > Dset.Tables[0].Rows.Count)
                {
                    // xử lý những trường hợp Học sinh vào lớp sau cùng.
                    foreach (DataRow NewRow in Set1.Tables[0].Rows)
                    {
                        CheckNewHs = true;
                        foreach (DataRow oldRow in Dset.Tables[0].Rows)
                        {
                            if (NewRow["NamtrehocId"].ToString() == oldRow["NamtrehocId"].ToString())
                            {
                                CheckNewHs = false;
                                break;
                            }
                        }
                        if (CheckNewHs == true)
                        {
                            Dset.Tables[0].Rows.Add(Dset.Tables[0].Rows.Count + 1, null, NewRow["NamtrehocId"], NewRow["LophocId"], NewRow["Hovaten"], NewRow["Tenthuonggoi"],
                        false, Ngaydiemdanh, ThanghocId, NamhocId);
                        }
                    }
                    // Kết thúc
                }
                ReturnSet = Dset;
            }
            else
            {
                ReturnSet = Set1;
            }
            return ReturnSet;
        }

        public DataSet SelectTblDiemdanhtredihoc(int NamhocId, int LophocId, int ThanghocId, DateTime Ngaydiemdanh)
        {

            DataSet ReturnSet = new DataSet();
            string NamePrc;
            if (LophocId == -1)
            {
                // NamePrc = QueryString.SPROC_TBLDIEMDANHTREDIHOC_GET2.Replace("@NamhocId", NamhocId.ToString()).Replace("@LophocId", LophocId.ToString()).Replace("@ThanghocId", ThanghocId.ToString()).Replace("@Ngay", Ngaydiemdanh.ToShortDateString());
                NamePrc = "SELECT TblTredihoc.Id, Vw_Tre_namtrehoc.NamtrehocId, Vw_Tre_namtrehoc.LophocId, Vw_Tre_namtrehoc.Hovaten, Vw_Tre_namtrehoc.Tenthuonggoi,  Vw_Tre_namtrehoc.Ngaysinh, Vw_Tre_namtrehoc.Gioitinh,Vw_Tre_namtrehoc.Tendantoc, TblTredihoc.Ngay, TblTredihoc.Trangthai, TblTredihoc.ThanghocId " +
                    "  FROM Vw_Tre_namtrehoc LEFT JOIN [SELECT TblDiemdanhtrehoc.Id, TblDiemdanhtrehoc.NamtrehocId, TblDiemdanhtrehoc.Ngay, TblDiemdanhtrehoc.ThanghocId, TblDiemdanhtrehoc.Trangthai FROM TblDiemdanhtrehoc " +
                    "  WHERE (((TblDiemdanhtrehoc.Ngay)=#" + Ngaydiemdanh.ToShortDateString() + "#) AND ((TblDiemdanhtrehoc.ThanghocId)=" + ThanghocId.ToString() + "))]. AS TblTredihoc ON Vw_Tre_namtrehoc.NamtrehocId = TblTredihoc.NamtrehocId";

            }
            else
            {
                //NamePrc = QueryString.SPROC_TBLDIEMDANHTREDIHOC_GET1.Replace("@NamhocId", NamhocId.ToString()).Replace("@LophocId", LophocId.ToString()).Replace("@ThanghocId", ThanghocId.ToString()).Replace("@Ngay", Ngaydiemdanh.ToShortDateString());
                NamePrc = "SELECT TblAntrongngay.Id AS TreantrongngayId, TblTrediemdanh.Id AS TredihocId, TblAntrongngay.NamtrehocId, TblAntrongngay.LophocId, TblAntrongngay.Hovaten, TblAntrongngay.Tenthuonggoi, TblAntrongngay.Ngaysinh, TblAntrongngay.Gioitinh, TblAntrongngay.Tendantoc, TblAntrongngay.Trangthai AS Diemdanhan, TblTrediemdanh.Trangthai AS Diemdanhdihoc, iif(isnull(TblAntrongngay.Ngay),#" + Ngaydiemdanh.ToShortDateString() + "#,TblAntrongngay.Ngay) AS Ngay, iif(isnull(  TblAntrongngay.ThanghocId)," + ThanghocId + ",TblAntrongngay.ThanghocId) AS ThanghocId " +
               " FROM [SELECT TblTredihoc.Id, Vw_Tre_namtrehoc.NamtrehocId, Vw_Tre_namtrehoc.LophocId, Vw_Tre_namtrehoc.Hovaten, Vw_Tre_namtrehoc.Tenthuonggoi,  Vw_Tre_namtrehoc.Ngaysinh, Vw_Tre_namtrehoc.Gioitinh, Vw_Tre_namtrehoc.Tendantoc, TblTredihoc.Ngay, TblTredihoc.Trangthai,TblTredihoc.ThanghocId   FROM Vw_Tre_namtrehoc LEFT JOIN (SELECT TblDiemdanhtrehoc.Id, TblDiemdanhtrehoc.NamtrehocId, TblDiemdanhtrehoc.Ngay, TblDiemdanhtrehoc.ThanghocId, TblDiemdanhtrehoc.Trangthai  FROM TblDiemdanhtrehoc   WHERE (((TblDiemdanhtrehoc.Ngay)=#" + Ngaydiemdanh.ToShortDateString() + "#) AND ((TblDiemdanhtrehoc.ThanghocId)=" + ThanghocId + "))) AS TblTredihoc ON Vw_Tre_namtrehoc.NamtrehocId = TblTredihoc.NamtrehocId  WHERE (((Vw_Tre_namtrehoc.LophocId)=" + LophocId + "))]. AS TblTrediemdanh INNER JOIN [SELECT  TblTrethamgiaan.Id, Vw_Tre_namtrehoc.NamtrehocId, Vw_Tre_namtrehoc.LophocId, Vw_Tre_namtrehoc.Hovaten, Vw_Tre_namtrehoc.Tenthuonggoi, Vw_Tre_namtrehoc.Ngaysinh, Vw_Tre_namtrehoc.Gioitinh, Vw_Tre_namtrehoc.Tendantoc, TblTrethamgiaan.Trangthai, TblTrethamgiaan.Ngay, TblTrethamgiaan.ThanghocId, Vw_Tre_namtrehoc.NamhocId " +
               " FROM Vw_Tre_namtrehoc LEFT JOIN (SELECT TblDiemdanhtrean.Id, TblDiemdanhtrean.NamtrehocId, TblDiemdanhtrean.Ngay, TblDiemdanhtrean.ThanghocId, TblDiemdanhtrean.Trangthai  FROM TblDiemdanhtrean  WHERE ( ((TblDiemdanhtrean.Ngay)=#" + Ngaydiemdanh.ToShortDateString() + "#) AND ((TblDiemdanhtrean.ThanghocId)=" + ThanghocId + "))) AS TblTrethamgiaan ON Vw_Tre_namtrehoc.NamtrehocId = TblTrethamgiaan.NamtrehocId " +
               " WHERE (((Vw_Tre_namtrehoc.LophocId)=" + LophocId + "))]. AS TblAntrongngay ON TblTrediemdanh.NamtrehocId = TblAntrongngay.NamtrehocId";
            }

            string NameTable = "TblDiemdanhtredihoc";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();

            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }

            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);

            return Dset;
        }
        
        public void Insert_Tredihoc(int NamtrehocId, int ThanghocId, DateTime Ngay, Boolean Tinhtrang)
        {
            bool Value = false;
            try
            {
                string NamePrc;
                if (Tinhtrang.ToString() == "True")
                {
                    NamePrc = QueryString.SPROC_TBLDIEMDANHTREDIHOC_ADD.Replace("@NamtrehocId", NamtrehocId.ToString()).Replace("@ThanghocId", ThanghocId.ToString()).Replace("@Ngay", Ngay.ToShortDateString()).Replace("@Trangthai", "-1");
                }
                else
                {
                    NamePrc = QueryString.SPROC_TBLDIEMDANHTREDIHOC_ADD.Replace("@NamtrehocId", NamtrehocId.ToString()).Replace("@ThanghocId", ThanghocId.ToString()).Replace("@Ngay", Ngay.ToShortDateString()).Replace("@Trangthai", "0");
                }
                OleDbCommand Cmm = new OleDbCommand();

                ClsConnection.OleDbDAdpter(NamePrc, Cmm);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Kiểm tra lại lệnh Insert");
                Value = true;
            }
        }
        
        public void Edit_Tredihoc(int Id, Boolean Tinhtrang)
        {
            bool Value = false;
            int i;
            if (Tinhtrang == false)
            {
                i = 0;
            }
            else
            { i = -1; }
            try
            {
                string NamePrc = QueryString.SPROC_TBLDIEMDANHTREDIHOC_EDIT.Replace("@Id", Id.ToString()).Replace("@Trangthai", i.ToString());
                OleDbCommand Cmm = new OleDbCommand();

                ClsConnection.OleDbDAdpter(NamePrc, Cmm);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Kiểm tra lại lệnh Edit");
                Value = true;
            }
        }
        
        public void Insert(int NamtrehocId, int ThanghocId, DateTime Ngay, Boolean Tinhtrang)
        {
            //   bool Value = false;
            int i;
            if (Tinhtrang == true)
            { i = -1; }
            else
            {
                i = 0;
            }
            try
            {
                string NamePrc = "INSERT INTO TblDiemdanhtrean (NamtrehocId, Ngay, ThanghocId, Trangthai) VALUES (" + NamtrehocId.ToString() + ", #" + Ngay.ToShortDateString() + "#, " + ThanghocId.ToString() + "," + i.ToString() + ") ";
                OleDbCommand Cmm = new OleDbCommand();
                ClsConnection.OleDbDAdpter(NamePrc, Cmm);
            }
            catch (Exception ex)
            {
                // MessageBox.Show("Kiểm tra lại lệnh Insert");
                // Value = true;
            }
        }

        public void Edit(int Id, int NamtrehocId, int ThanghocId, DateTime Ngay, Boolean Tinhtrang)
        {
            int i;
            if (Tinhtrang == true)
                i = -1;
            else
                i = 0;
            try
            {
                string NamePrc = "UPDATE TblDiemdanhtrean SET NamtrehocId = " + NamtrehocId.ToString() + ", Ngay = #" + Ngay.ToShortDateString() + "#,  	ThanghocId = " + ThanghocId.ToString() + ", Trangthai = " + i.ToString() + "  WHERE Id = " + Id.ToString() + " ";
                OleDbCommand Cmm = new OleDbCommand();
                ClsConnection.OleDbDAdpter(NamePrc, Cmm);
            }
            catch (Exception ex)
            {
            }
        }

        //public void UpdateTienan(int Id, float Tienan)
        //{
        //    bool Value = false;
        //    try
        //    {
        //        string NamePrc = "sproc_TblDiemdanhtrean_UpdateTienan";
        //        SqlCommand Cmm = new SqlCommand();
        //        Cmm.Parameters.Add(new SqlParameter("@Id", Id));
        //        Cmm.Parameters.Add(new SqlParameter("@Tienan", Tienan));
        //        ClsConnection.SqlDAdpter(NamePrc, Cmm);
        //    }
        //    catch (Exception ex)
        //    {
        //        MessageBox.Show("Kiểm tra lại lệnh Edit");
        //        Value = true;
        //    }
        //}
    }
}
